#!/usr/bin/env python3
# -*- coding: utf-8 -*-

from openpyxl.styles import PatternFill
from Logger import logger
from Timer import timer
import openpyxl


class ParseExcel:
    """解析Excel表，提供操作Excel表格的基础方法"""
    @timer()
    def __init__(self, file_path):
        self.file_path = file_path
        self.file_name = self.file_path.split('\\')[-1]
        self.workbook = openpyxl.load_workbook(self.file_path, data_only=True)
        self.worksheet_names = self.workbook.sheetnames
        self.attr_group = self.load_excel_list()

    def get_worksheet(self, sheet_name):
        try:
            return self.workbook[sheet_name]
        except KeyError:
            logger.error('工作表[{0}]不存在'.format(sheet_name))

    def set_cell_background_color(self, sheet_name, row, column, fg_color):
        fill = PatternFill(fgColor=fg_color, fill_type='solid')
        ws = self.get_worksheet(sheet_name)
        ws.cell(row=row, column=column).fill = fill

    @timer(tracer=False)
    def save_workbook(self):
        try:
            self.workbook.save(self.file_path)
        except PermissionError:
            logger.error('表格[{0}]正在被使用，无法保存，请关闭表格重试'.format(self.file_name))

    @staticmethod
    def _make_attr_dict(key_list, value_list):
        """
        根据key列表和value列表生成字典，字典中的value不能是'N/A'，字典中的key,value对不能同时为空字符串""
        :param key_list: key 列表
        :param value_list: value 列表
        :return: value不是'N/A'的字典
        """
        dic = dict()
        _value_list = value_list[:len(key_list)]
        for index, value in enumerate(_value_list):
            if value is not None and value != 'N/A' and key_list[index] is not None:
                dic[key_list[index]] = str(value)
        return dic

    @timer(tracer=False)
    def load_excel_list(self):
        """
        遍历excel表格的sheet页，再遍历每一行，如果任意行的第一个单元格的内容是'>>XPath'，则说明这一行是属性名称，
        如果第一个单元格的内容包含'>>'字符且内容不是'>>XPath'，则说明这一行是属性值。
        :return: 一份列表，包括表格的sheet名，所在行，属性名和属性值对应的字典
        """
        wb = self.workbook
        attr_list, val_list, attr_group = [], [], []
        for ws_name in self.worksheet_names:
            ws = wb[ws_name]
            for row_num, c_row in enumerate(ws.iter_rows()):
                tag = c_row[0].value
                if tag == '>>Commentary':
                    comment = c_row[1].value
                    comment_dic = {'SheetName': ws_name, 'RowNum': str(row_num + 1), '>>Commentary': comment}
                    attr_group.append(comment_dic)
                elif tag == '>>XPath':
                    attr_list = ['SheetName', 'RowNum']
                    for cell in c_row:
                        if cell.value:
                            attr_list.append(cell.value)
                elif tag and '>>' in tag:
                    val_list = [ws_name, str(row_num + 1)]
                    for cell in c_row:
                        val_list.append(cell.value)
                    attr_dic = self._make_attr_dict(attr_list, val_list)
                    attr_group.append(attr_dic)
        # logger.info('解析工作簿[{0}]成功'.format(self.file_name))
        return attr_group

    # @timer(tracer=False)
    # def load_excel_list(self):
    #     """
    #     遍历excel表格的sheet页，再遍历每一行，如果任意行的第一个单元格的内容是'>>XPath'，则说明这一行是属性名称，
    #     如果第一个单元格的内容包含'>>'字符且内容不是'>>XPath'，则说明这一行是属性值。
    #     :return: 一份列表，包括表格的属性名和属性值对应的字典
    #     """
    #     wb = self.workbook
    #     attr_list, val_list, attr_list = [], [], []
    #     for ws_name in self.worksheet_names:
    #         ws = wb[ws_name]
    #         for c_row in ws.iter_rows():
    #             tag = c_row[0].value
    #             if tag == '>>Commentary':
    #                 comment = c_row[1].value
    #                 comment_dic = {'>>Commentary': comment}
    #                 attr_list.append(comment_dic)
    #             elif tag == '>>XPath':
    #                 attr_list.clear()
    #                 for cell in c_row:
    #                     if cell.value:
    #                         attr_list.append(cell.value)
    #             elif tag and '>>' in tag:
    #                 val_list.clear()
    #                 for cell in c_row:
    #                     val_list.append(cell.value)
    #                 attr_dic = self._make_attr_dict(attr_list, val_list)
    #                 attr_list.append(attr_dic)
    #     # logger.info('解析工作簿[{0}]成功'.format(self.file_name))
    #     return attr_list


if __name__ == '__main__':
    import os
    objects_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'ShopConfig.xlsx')
    objects_obj = ParseExcel(objects_path)
    a = objects_obj.load_excel_list()
    for i in a:
        print(i)
